package com.bw.dao;

import com.bw.bean.Sugar1;
import com.bw.bean.Sugar2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("select p.`name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN base_province p ON o.province_id=p.id\n" +
            "GROUP BY p.`name`")
    List<Sugar1> getList1();

    @Select("select t.`name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id \n" +
            "LEFT JOIN base_trademark t ON s.tm_id=t.id \n" +
            "where t.`name` is not null\n" +
            "GROUP BY t.`name`")
    List<Sugar1> getList2();

    @Select("select p.spu_name `name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON d.order_id=o.id \n" +
            "LEFT JOIN sku_info s ON s.id=d.sku_id\n" +
            "LEFT JOIN spu_info p ON p.id=s.spu_id\n" +
            "where p.spu_name is not null \n" +
            "GROUP BY p.spu_name")
    List<Sugar1> getList3();

    @Select("select * from \n" +
            "(select count(*) 'name' from user_info where TIMESTAMPDIFF(month,create_time,now()) <14) a,\n" +
            "(select count(*) 'value' from user_info where TIMESTAMPDIFF(month,create_time,now()) >=14) b")
    List<Sugar1> getList4();

    @Select("select c2.`name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON s.id=d.sku_id\n" +
            "LEFT JOIN base_category3 c3 ON s.category3_id=c3.id \n" +
            "LEFT JOIN base_category2 c2 ON c3.category2_id=c2.id \n" +
            "WHERE c2.`name` is not null\n" +
            "GROUP BY c2.`name`\n")
    List<Sugar1> getList5();

    @Select("select CONCAT(SUBSTR(u.birthday,3,1),'0后') name,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN user_info u ON o.user_id=u.id\n" +
            "GROUP BY CONCAT(SUBSTR(u.birthday,3,1),'0后')")
    List<Sugar1> getList6();

    @Select("select yys 'name',sum(final_total_amount) `value` from order_info where yys is not null group by yys\n")
    List<Sugar1> getList7();

    @Select("select t.`name`,\n" +
            "IF(u.gender='F','男','女') gender,\n" +
            "sum(o.final_total_amount) `value`\n" +
            "from order_info o \n" +
            "LEFT JOIN user_info u ON o.user_id=u.id \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id \n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id \n" +
            "LEFT JOIN base_trademark t ON s.tm_id=t.id \n" +
            "where t.`name` is not null\n" +
            "GROUP BY t.`name`,u.gender")
    List<Sugar2> getList8();

    @Select("\n" +
            "call spTwo")
    Double getList9();

    @Select("select p.spu_name name,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON s.id=d.sku_id\n" +
            "LEFT JOIN spu_info p ON s.spu_id=p.id\n" +
            "where p.spu_name is not null\n" +
            "GROUP BY p.spu_name\n")
    List<Sugar2> getList10();
}
